package com.mysql.imples;



import com.mysql.dao.Staffsdao;
import com.mysql.entity.Staffs;
import com.mysql.helpers.DBHelper;
import com.mysql.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 作者:wmx  2025-02-08 11:26
 * 1、功能；通用数据交互层-staffs表

 **/
public class Staffsimple implements Staffsdao<Staffs>{
    private static Connection connection= null;
    DBHelper dBhelper = new DBHelper();



    /**
     * 功能:mysql-操作数据
     * @param sql  sql语句
     * @return 返回sql防注入语句
     */
    public  ArrayList<Staffs>  executeDatapreparedStatement() {
        String sql = " select `id`,`name`,`age`,`phone`,`sta_pos`,`add_time`,`update_time`from staffs ";
        ArrayList<Staffs> staffs  = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);

            staffs = dBhelper.executeDatabase(preparedStatement);//执行数据库交互操作

        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }  finally {
            // 在这里统一关闭 PreparedStatement 和 Connection
            try {
                DBUtil.getClose(connection,null,preparedStatement);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return staffs;
    }

    /**
     * 功能:mysql-操作员工数据(添加)
     * @param sql  sql语句
     * @param object 员工信息
     * @return 返回是否修改操作
     */
    public Integer executeDateInsert( Object object) {
        String sql = " insert into staffs values(default,?,?,?,?,default,default)";
        Staffs staffs = (Staffs) object;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,staffs.getName());
            preparedStatement.setInt(2,staffs.getAge());
            preparedStatement.setString(3,staffs.getPhone());
            preparedStatement.setString(4,staffs.getStaPos());
        } catch (SQLException e) {
            e.printStackTrace();
            return  -1;
        }
        return dBhelper.executeDatabaseUpdate(preparedStatement);
    }


    /**
     * 功能:mysql-操作数据(删除、修改)
     * @param sql  sql语句
     * @param id   序号
     * @param int 返回是否执行成功
     * @return
     */
    public Integer  executeDateDelete(int id) {
        String sql = "delete from staffs where `id` = ?";
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
        } catch (SQLException e) {
            e.printStackTrace();
            return  -1;
        }
        return dBhelper.executeDatabaseUpdate(preparedStatement);
    }




    /**
     * 功能:mysql-操作员工数据(修改)
     * @param sql  sql语句
     * @param object 员工信息
     * @return 返回是否修改操作
     */

    public Integer executeDateUpdate(Staffs object) {
        String sql = "update staffs set  `name` = ? ,`age` = ? ,`phone` = ? ,`sta_pos` = ?   where `id` = ?";
        Staffs Staffs = (Staffs) object;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,Staffs.getName());
            preparedStatement.setInt(2,Staffs.getAge());
            preparedStatement.setString(3,Staffs.getPhone());
            preparedStatement.setString(4,Staffs.getStaPos());
            preparedStatement.setInt(5,Staffs.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }

        return dBhelper.executeDatabaseUpdate(preparedStatement);
    }


    /**
     * 功能:mysql-获取员工数据模糊查询总数据
     * @param sql  sql语句
     * @param query 模糊查询条件
     * @return 返回员工数据模糊查询总数据
     */
    public  ArrayList<Staffs>  executeDataQuery( String query) {
        String sql = " select `id`,`name`,`age`,`phone`,`sta_pos`,`add_time`,`update_time` from staffs \n" +
                " where \n" +
                "`id` like ? or\n" +
                "`name` like ? or\n" +
                "`age` like ? or\n" +
                "`phone` like ? or\n" +
                "`sta_pos` like ? or\n" +
                "`add_time` like ? or\n" +
                " `update_time` like ? ";
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i < 8; i++) {
                preparedStatement.setString(i,"%" + query + "%");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return dBhelper.executeDatabase(preparedStatement);
    }


    /**
     * 功能:mysql-获取员工数据总数
     * @param sql sql语句
     * @return 返回员工数据总数
     */
    public Integer executeDateCount() {
        String sql = "select count(`id`) from staffs";
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return dBhelper.executeDatabaseCount(preparedStatement);
    }

    /**
     * 功能:mysql-获取员工数据模糊查询总数据
     * @param sql  sql语句
     * @param query 模糊查询条件
     * @return 返回员工数据模糊查询总数据
     */
    public Integer executeDataQueryCount(String query) {
        String sql = " select count(`id`) from staffs \n" +
                " where \n" +
                "`id` like ? or\n" +
                "`name` like ? or\n" +
                "`age` like ? or\n" +
                "`phone` like ? or\n" +
                "`sta_pos` like ? or\n" +
                "`add_time` like ? or\n" +
                " `update_time` like ? ";
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i < 8; i++) {
                preparedStatement.setString(i,"%" + query + "%");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return dBhelper.executeDatabaseCount(preparedStatement);
    }


}
